Copyright: Copyright © 1998 Ability Plus Software (UK) Ltd.


What is Spreadsheet?

A spreadsheet is a tool you can use to organize, track and calculate financial and numeric information. You can use a spreadsheet to analyze figures, calculate totals or averages, or project business trends. In addition, you can use spreadsheets to experiment and see what happens when the numbers change in “what if” scenarios.

You can use spreadsheets to solve complex problems (like calculating mortgage repayments), as well as simpler problems (like calculating miles to the gallon). Here are some more examples of how you can use a spreadsheet:

•	Create a yearly budget

•	Analyze quarterly sales figures

•	Set sales quotas

•	Analyze stock investments

•	Project loan repayment amounts

•	Compute interest payments at variable rates

•	Project income and profit figures

Where to go next:

Getting started with Spreadsheet

Entering information

Formatting information

Copying and moving information

Turning data into a chart


Starting Spreadsheet

To start Spreadsheet, follow these steps:

1.	Click the Start button on the Windows Taskbar

2.	Select Programs

3.	Select Ability Office 98

4.	Select Ability Spreadsheet

Spreadsheet will open with a new spreadsheet ready for entering data into.

See also:

Parts of the spreadsheet

Moving around the spreadsheet

Spreadsheet definitions


The spreadsheet window

The area in which you work on a spreadsheet is called the spreadsheet window. The different areas of the spreadsheet window are shown on this diagram:

Clicking on a column letter on the column bar selects that column, while clicking on a row number on the row bar selects that row. You can select multiple rows or columns by dragging the cursor across several row or column bars or you can select the entire sheet by clicking the button at the intersection of the row and column bars.

A spreadsheet is organized into rows and columns, like a table. The rows are numbered sequentially down the left edge of the spreadsheet window. The columns are labeled with letters across the top of the spreadsheet window. (The column letters run sequentially from A to Z, then from AA to AZ, then from BA to BZ, and so on, finally ending with IA to IV.)

The maximum size for a spreadsheet is 65,536 rows and 256 columns.

See also

The toolbars and formatting bar

The formula bar


The toolbars and formatting bar

Across the top of the window are the various toolbars for spreadsheet. These contain buttons and drop-down lists which enable you to format and manipulate the cells and figures quickly and easily, without needing to open the menus.

Note:	You can choose not to display the toolbar and/or the formatting bar. To do this, click on the Toolbars option from the View menu. Click on the Standard checkbox to turn the toolbar on or off, and on the Format checkbox to turn the formatting bar on or off. When the feature is shown, a check is put next to it; when it is not shown, the check is removed.


The formula bar

The formula bar is where you enter text, numbers and formulas into cells and fields.

The formula bar shows the name of the current cell and what type of information it contains. You can click anywhere within the contents of the formula bar to edit the spreadsheet cell.

There are three buttons to assist in editing the cell contents:

•	Confirm button [image] or Enter key. Saves the contents of the formula bar into the spreadsheet.

•	Cancel button [image] or Esc key. Aborts the current edit and returns the cell back to its previous state.

•	Functions button [image]. Calls a on a list of over 200 built-in functions. For more information, see function reference.


Coolbars mode and large buttons

You can choose the way toolbars appear. Coolbars are normal toolbars but with the borders around the buttons removed.

Large Buttons are merely larger versions of normal toolbar buttons with descriptive text added.

To switch to coolbars mode and/or large buttons, select Toolbars from the View menu. Click on the Coolbars Mode and/or Large Buttons checkbox to turn these on or off.


Moving around a spreadsheet

To move around a spreadsheet, you would usually use the mouse and click to put the cursor where you want it. You can also use the scroll bars at the right and bottom of the screen to move either vertically or horizontally through the spreadsheet.

However, you can also move the cursor with the cursor movement keys, and there are times when this can be quicker. The following table shows all the ways you can move the cursor.

Key	Description
Arrow keys	The arrow keys move the cursor one cell at a time in the direction of the arrow (up, down, left, or right).
page up	Moves up one screen at a time.
page down	Moves down one screen at a time.
home	Moves to column A in the current row.
end	Moves to the last column that contains information in the current row, or to the last column of the whole spreadsheet if the current row contains no information.
tab	Moves one cell to the right.
shift + tab	Moves one cell to the left.
ctrl + home	Moves to the first cell (A1) in the spreadsheet.
ctrl + end	Moves to the intersection of the last column and the last row that contain information.
ctrl + page up	Moves one screen to the left.
ctrl + page down	Moves one screen to the right.
ctrl +	Moves to the next cell on the right that contains information.
ctrl +	Moves to the next cell on the left that contains information.
ctrl +	Moves to the next cell upwards that contains information.
ctrl +	Moves to the next cell downwards that contains information.


Going to a particular cell

If you are working on a small spreadsheet, it is easy to find the cell you require (for formatting, deleting, hiding, or whatever). However, if you have a very large spreadsheet, it can be quicker to ask Spreadsheet to find the cell (or range of cells) you want.

Select the Go To command from the Edit menu. The “Go To” dialog is displayed.

In the Category box, click on the location type. The categories include typical spreadsheet locations such as cells, named ranges, columns and rows. Next enter the location you wish to go to ( you can choose most of these from the drop down box). Then click on the OK button, and the spreadsheet will be redrawn, with the location you asked for selected and active.


Spreadsheet definitions

There are certain terms which are used in virtually all spreadsheet programs. It is worth becoming familiar with them, as it will make your spreadsheet work much simpler:

Cells

Cell addresses

Cell ranges


Cells

The spot where a row and column intersect is called a cell. A cell is the place in a spreadsheet where you enter text, numbers, or formulas. For example, in the following illustration, January appears in the cell C4, where column C and row 4 intersect:

The active cell is shown by a heavy cell outline - in this example, B2.

Cells can also be called fields (the word cell is part of the special spreadsheet vocabulary), and you use fields throughout Ability – in Write, and Database – and they always work in similar ways and can call on the same function list. You can apply what you learn about fields (known here as cells) in Spreadsheet to the fields you work with in other applications.


Cell addresses

Each cell in a spreadsheet has a unique identifier, called a cell address. The cell address tells you the exact location of information, just as a home address such as 12 Maple Avenue pinpoints a specific house.

A cell address consists of a column letter and a row number. For example, the cell located where column C and row 2 intersect has the cell address C2.


Cell ranges

A range is simply a group of cells. For example, the cells A1, A2, A3, A4, A5 can be referred to individually when using them in a formula but it is quicker to use a range. The following formulas, which give the same result, demonstrate this:

=SUM(A1, A2, A3, A4, A5)

=SUM(A1..A5)

You can give a cell range a unique name and refer to it in formulas using the name rather than specifying the first and last cells in the range. You can also treat fields in a database table as ranges so that, for example, you can sum all the records in an invoice database.


Creating a spreadsheet

To create a new spreadsheet, click on the New [image] button on the toolbar or select the New command from the File menu. A new blank spreadsheet will be opened in the Spreadsheet window.

When you first open a spreadsheet, it is given a default page set-up. This means that the paper size and margins are already set for you. For the majority of spreadsheets, these settings will probably be what you want, but you might want to change them, for instance to give a large left margin on a document that is going to be bound. See Page set-up.

See also:

Entering information.


Opening an existing spreadsheet

To open an existing spreadsheet, first start Spreadsheet - see Starting Spreadsheet.

Next click on the Open button [image] on the toolbar or select Open from the File menu.

The "Open" dialog is displayed. When you have located the spreadsheet document you require (you need to locate the appropriate folder in the Look In box and set the Files of Type to Ability Spreadsheet, if it is not so set already), click on the Open button to open it.


Entering information

You can put two main types of information into a spreadsheet: text and numbers. Ability will automatically sense which type of information you are entering and give it a default justification within the cell: when you enter text, Ability will left justify your entry; when you enter numbers, Ability will right justify them.

Here are some examples of the type of information you can enter and the way Ability deals with them:

Enter this text	Spreadsheet displays	Type of information
Hello world!		Text
123		Number
$123,000.00		Currency
12/12/98		Date
In this example, Currency and Date are actually numbers with a format applied. There are other types of number formats and you can change the way Ability displays the result. See formatting a value.

Any information you type will be put into the cell which has the dark border around it. This is the active cell. To activate a cell, click on it, or move the dark border to it by using the arrow keys.

See:

Entering text

Entering values

Entering dates


Entering text

When you start typing text, you will notice that it does not appear in the active cell. Instead, it is shown in the formula bar. This means that you can make changes to it more easily, as it is slightly larger and easier to see.

When you have finished typing the text, do one of the following:

•	Click on the Confirm button [image] on the formula bar. The text will be inserted into the active cell.

•	Press one of the arrow keys. Your text will be entered and the active cell will move according to which arrow key you pressed. This saves a keystroke if you’re entering a long column or row of information.

•	Press the Enter key - the text will be entered and the active cell will move down one row.

•	Click the Cancel button [image] on the formula bar to abandon the text entry.

Ability will display the word “Text” in the Cell Type box on the formula bar.

The text you enter can be wider than the cell - see entering a long text item.


Entering a long text item

When you create a new spreadsheet, each column is 80 points wide by default.

A point in this case is 1/96 of an inch or 0.026cm and is not the same as the typographical measure (where a point is 1/72 of an inch and used to measure font size).

If the text you want in the cell is longer than the default width, you can enter the text and let it overflow into the next cell. If there is no information in the cell to the right, the whole text is displayed, otherwise Ability ‘clips’ the text at the cell boundary.

An alternative is to allow the text to be wrapped within the cell - see cell alignment.


Entering values

You enter values in the same way as you enter text: point to the cell where you want the value to appear, then type it in. Finish the entry in the same way as you would finish a text item - see entering text

Ability will display the word “Number” in the Cell Type box on the formula bar.

Note:	Values are automatically put to the right of cells – this is to make sure that columns of figures line up neatly. You can change this alignment if you prefer; see cell alignment.


Entering currency values

You can include a currency symbol when entering numbers: Ability will understand that you want a currency format. All these are valid currency entries:

$12
$123,456
$123,456.00

Note:	The currency symbol you use must match the setting in the Windows Control Panel. For example, this will be the dollar ($) if you are in the US and the pound (£) in the UK. If not, Ability will treat the cell as being text and you won’t be able to perform arithmetic on it.


Entering a time

Use a colon to separate hours from minutes. Here are some examples of entries that Ability will recognize as time:

23:45
11:45 AM
11:45 PM

For a description of how Ability deals with dates and time, see: date and time functions


Entering dates

You should use the shorthand method to enter dates (if you prefer the long display for dates, change the format – see formatting a date ).

These are all OK to enter:

3-6-64
12/2/1995
01.01.95
September 12, 1995

But these will be treated as text only:

3rd June 1964
September 12th , 1995

If you don’t use the shorthand method, Ability could treat your entry as text and you won’t be able to change the format or perform date arithmetic.

You can enter the year as two or four digits. Two digit years entered below 30 will default to the 21st century. For example:

Enter	Date stored as
1/1/20	1/1/2020
1/1/29	1/1/2029
1/1/30	1/1/1930
For a description of how Ability deals with dates and time, see: date and time functions


Entering numbers you don't want to calculate

Whenever you enter a number, Spreadsheet assumes that it is a value and that you might want to use it in a calculation.

If you enter a number that is obviously not meant to be used in calculations, such as a phone number like 6684123, Spreadsheet can't automatically tell that it is supposed to be text. So, to enter numbers like phone or invoice numbers, you need to tell Spreadsheet not to calculate them.

To do this, type a single quote (‘) before you enter the actual number. This is necessary if you want a formula expression, such as =26 - 11, to appear as text rather than returning the result of a calculation. In this case, type '=26 - 11.

The single quote doesn’t show up in the spreadsheet. It simply indicates that the number should be treated as text rather than as a value.


Formatting the spreadsheet

Whether you enter text or a number into a cell, you can specify how you want the cell contents to appear. You can format a single cell, or range of cells in the same way. Choose from:

•	Font, font style, size and color

•	Cell alignment

•	Borders

•	Background colors

If you are formatting numbers, you get additional options to deal with the type of numbers you are using: currency, decimal places, scientific, date and so on. See formatting a value.

Note:	When you format a cell and copy it, the formatting is copied also.


Cell font and style

Use any or all of the following methods to select cell font and style using the toolbars:

•	Click on the arrow to the right of the Font box [image] to choose a font.

•	Click on the arrow to the right of the Size box [image] to choose a size (or type a size directly into the Size box).

•	Click on the Bold, Italic, Underline and Strikethrough buttons [image] to apply styling.

The Bold, Italic, Underline and Strikethrough buttons have two states: up and down. If the active cell shows a button down, the effect has been selected, up and the effect is turned off.

You can also choose fonts using the menus.


Using the menus to select cell font and style

To choose a font, select the Font command from the Format menu. (The Format menu can also be accessed by right clicking your mouse and select Format). The “Font” dialog box is displayed.

•	Choose a font for the text in the Font box, and select a style in the Font Style box.

•	Choose a size for the text in the Size box.

•	Choose to strikeout and/or underline text in the Effects box: click on the checkbox next to the effect(s) you require.

•	Select a color for text in the Color box.

A sample of the options you have chosen is given in the Sample box. When you have finished, click on the OK button and the text will be formatted to your specification.


Cell alignment

By default, Ability will align text to the left and numbers to the right. Both will be aligned to the bottom of the cell and each cell is formatted to sit on a single line (i.e. cell wrapping is off).

You can override the alignment Ability sets by using either the formatting bar or the Alignment command.

Using the formatting bar you can:

•	Click on the Left, Right or Center buttons [image] to specify horizontal alignment.

•	Click on the Top, Middle or Bottom buttons [image] to choose vertical alignment. Depending on the size of the cell, this might not make much difference to the appearance of the text.

•	Click on the Wrap button [image] to wrap the text within the cell. This switches the cell from a single line to as many lines as it needs to fit all the text within the cell.

Alternatively, you can select the Alignment command from the Format menu (also available by right clicking your mouse). The “Alignment” dialog box is displayed.

To set the horizontal alignment of the text, click on the button next to Left, Center or Right.

To set the vertical alignment of the text, click on the button next to Top, Center or Bottom.

If you want a label that is too long for the cell to wrap round onto a line below within the cell (rather than writing on into adjacent cells), click on the checkbox next to Wrap Text. The cell will look something like this:

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the text will be formatted to your specification.


Formatting a value

There are a number of formats that can be applied to cells that will be effective if the cell contains any form of numeric value (including plain numbers, dates, formulas, currency).

The quickest way is to use the number format buttons on the toolbar as follows:

 - Add currency format to cell.

 - Add percent format to cell.

 - Format thousands with separator.

 - Increase number of decimal places by one.

 - Decrease number of decimal places by one - the numbers will be rounded to the nearest decimal place.

These buttons can be used together - for example, a cell may contain the number 32. Click on the Currency button then twice on the Increase decimal button to display $32.00.

For more detailed number formatting, select the Number command from the Format menu (also accessible using right click). The “Number” dialog box will be displayed.

To format the appropriate type of value, select it from within the list. The settings displayed to the right of the list change according to your selection. For details on how to modify these settings for each type of value, see one of the below:

Number

Currency

Date

Time

Percentage

Fraction

Scientific

Boolean


Formatting a number

To format a number:

1.	Select the Number command from the Format menu.

2.	Select Number from the value type list. The “Number” group box will display.

3.	In the Decimal Places box, specify the precision of the number. You can have a minimum of none and a maximum of fifteen. (You can also use the Increment precision button on the formatting toolbar.)

4.	Select the Thousands With Commas checkbox to get commas in the number, i.e. whether to have 1,345,622 or 1345622. (You can also use the Commas button on the formatting toolbar.)

5.	Select the Negatives In Brackets checkbox to enclose negative numbers in brackets, i.e. whether to have (123456) or -123456. Surrounding negative numbers with brackets is a common practice in accountancy.

6.	Select the Negative In Red checkbox to display negative numbers in a red font.

7.	In the Trail/Lead String option, you can choose to have either trailing or leading characters. Enter your text in the text box or choose one of the existing group. You can create your own number formats, including alternative currency formats. Here are some examples:

2 lb.
2.2 Kg
1.2 $bn
FFr 23.56
DM 23.56

Note: Trailing and Leading text is managed in the Options dialog. You can add and delete entries here. See general options for more details.

A sample of the options you have chosen is given in the Preview box. When all is set, click on the OK button and the number will be formatted to your specification.


Formatting a currency

To format a number as currency:

1.	Select the Number command from the Format menu.

2.	Select Currency from the value type list. The “Currency” group box will display.

3.	The currency options displayed are the same as for a general number format - see formatting a number for details on how to set these.

You can also use the Currency button [image] to add a currency symbol directly.

Note: 	The currency symbol used by Ability is taken from the Regional settings in the Windows Control Panel.


Formatting a time

To format a time:

1.	Select the Number command from the Format menu.

2.	Select Time from the value type list. The “Time” options box will be displayed.

3.	Click on the checkbox next to Seconds to display seconds as well as hours and minutes.

4.	Click on the checkbox next to 0..12 to use the 12-hour clock and thereby display a trailing AM/PM. Leave the checkbox blank to use the 24-hour clock.


Formatting a date

To format a date:

1.	Select the Number command from the Format menu.

2.	Select Date from the value type list. The “Date” group box will display.

3.	In the Month group box, specify how you would like the month represented:

Digits:	1
Short Name:	Jan
Long Name:	January

4.	Choose the separator you wish to appear between the different elements of the date in the Separator group box (e.g. 15/7/97 or 15-7-97)

5.	In the Year group box, choose between None, Short Form (e.g. 97), or Long Form (e.g. 1997).

6.	In the Order group box, choose whether you would like the day or the month to come first.

You can also choose to display the time alongside the date: click on the checkbox next to Add Time and then choose the time format.

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the date will be formatted to your specification.


Formatting a percentage

To format a number to display as a percentage:

1.	Select the Number command from the Format menu.

2.	Select Percent from the value type list. The “Percent” group box will display.

3.	The percent options displayed are the same as for a general number format - see formatting a number for details on how to set these.

Note: you can also use the Percentage button [image] on the formatting toolbar to add percentage formatting.


Formatting a fraction

To format a real number as a fraction:

1.	Select the Number command from the Format menu.

2.	Select Fraction from the value type list. The “Fraction” group box will display.

3.	In the Display As box, choose between Auto (the nearest representation of the number as a fraction) or round the fraction to the nearest 100th, 10th, 16th, 8th or quarter.

You can also choose to have the integer part of fractional numbers displayed with commas, negatives in brackets or in red, as with other numbers - see formatting a number.

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the fraction will be formatted to your specification.


Formatting a scientific number

To format a number to display in scientific notation:

1.	Select the Number command from the Format menu.

2.	Select Scientific from the value type list and set the decimal place as described in formatting a number.

Ability defaults to Scientific format if the number is too big (or small) to display with significant precision in the width of the current cell.

Note: scientific format coverts the original number into a base number, between 1 and 10, multiplied by a power of 10. This is useful for working with very large or very small numbers. For example, 1 meter is defined as the distance light travels in 0.000000003335640952 seconds. Entering such a value is hard on the eye. Selecting scientific format gives you a simplified display of 3.34E-09 (which is 3.34 x 10-9).


Formatting a boolean value

To format a number as boolean:

1.	Select the Number command from the Format menu.

2.	Select Boolean to display either TRUE, for all numbers that are non-zero, or FALSE, for cells containing zero.


Clearing a format

To clear a format from a cell or range of cells, select the cell or range and then right click your mouse. Select Clear and then select the Format option.

Alternatively, select the Clear command from the Edit menu. Select the Format option in the same way.


Selecting the background color

To change the background color of a cell, or range of cells, select the Color command from the Format menu. The “Color” dialog is displayed.

Select the Color button to access a variety of background colors. Clicking on a color button will select that color.

Note that you can select finer tones of color by adjusting the amount of red, green or blue that a color contains. To do this use the boxes marked R-value, G-value and B-value. The intensity of red, green or blue in a particular color runs from 0 to 255.


Adding borders

To add a border to the spreadsheet:

1.	Select the cell or range of cells around which you wish to put the border.

2.	Click on the Format Border button [image] on the formatting bar or select the Border command from the Format menu. The “Borders” dialog is displayed.

3.	In the Border group box, you specify which edges of the spreadsheet you would like bordered: you can choose Left, Right, Top, Bottom (which act on each cell) or Outline (which puts a box around the whole of the selected area). Using all of these together, you can have, say, a thick lined box of one color that contains broken lined cells of another color.

4.	In the Style group box, you choose the line style you would like the border to have.

The easiest way to set your borders is to pick a line style first and then click on each of the border edges in turn. Often you’ll want a different outline style, so click on the outline box and then click on a different line style to finish. For example, to set a border style that looks like this:

Go through the following steps:

1.	Click the Border button on the toolbar.

2.	Select a thin line style.

3.	Click on Left, Right, Top and Bottom border boxes.

4.	Click on the Outline box.

5.	Select a heavier line style.

6.	Click OK to finish.

See also:

Selecting the border color

Removing borders.


Border colors

In the Color group box, choose the border color. You can use different colors for each edge of the border or for the outline. The default setting, Automatic, will default to black lines when you first create a border. If you choose other colors, these will be preserved until you decide to change them in subsequent edits.

You can edit the color and/or style of each border quite easily. Re-enter the Border dialog and select the color and style as appropriate for each border.

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the border will be drawn to your specification.


Removing borders

Removing a border is a case of setting the line style to blank:

1.	Select the cells you want to change.

2.	Click on the Format Border button [image] or select the Border command from the Format menu.

3.	Click on the blank line style and then click on each of the border boxes in turn.

4.	Click on the OK button when you have finished.

Note: if you don’t mind loosing other formatting in the cells, you can short-cut this process by simply selecting the Clear command from the Edit menu and choosing the Format option.


Protecting a spreadsheet

To “protect” a spreadsheet, that is to prevent changes to the contents and format of locked cells and the contents of hidden cells to be displayed:

1.	Select the Protect command from the Tools menu. The “Protect” dialog box is displayed.

2.	Type in a password. You will not see the letters; they are represented by asterisks to prevent anyone seeing your password accidentally.

3.	Click on the OK button, and you are asked to re-enter your password as a check.

Note: You can protect a spreadsheet without entering a password. To do this, just click on the OK button of the “Protect” dialog box without entering a password.

Write your password down in a safe place. If you forget your password, you will never be able to unprotect your spreadsheet!

See also:

Locking and hiding cells

Unprotecting a spreadsheet


Unprotecting a spreadsheet

To unprotect a spreadsheet, select the Unprotect command from the Tools menu. The “Protect” dialog box is displayed. Type in the password with which you protected the spreadsheet, and click on the OK button. You can now make alterations to any locked or hidden cells in the spreadsheet.


Locking and hiding cells

When you protect a spreadsheet, it is only the locked and hidden cells which are affected. There is a difference between the two states:

•	Locking a cell prevents the cell from being changed when the spreadsheet is protected.

•	Hiding a cell means that its value will not be displayed, either in the cell itself or on the status bar, when the spreadsheet is protected. You’d probably also want to lock any hidden cells.

These features can be useful, for instance, if someone else uses your spreadsheet and you do not want them to accidentally alter values or formulas in certain fields or view sensitive information.

To lock or hide cells:

1.	Select the cell or range of cells you want to lock or hide.

2.	Click on either or both of the Lock and Hide buttons [image] on the formatting bar.

Alternatively, choose the Attributes command from the Format menu. The “Cell Properties” dialog is displayed.

Click on the checkbox next to Lock to lock the cells, and on the one next to Hide to hide the cells. You can choose both options, that is to lock and hide the cells. Click on the OK button.

The cells will be redrawn with hatching over them, to help you remember which cells have been locked, or hidden, or both while you are designing your spreadsheet. When the spreadsheet is protected (see protecting a spreadsheet) the hatching disappears.

You can unlock and “unhide” cells again, simply by selecting them and clicking on the Lock and Hide buttons on the formatting bar or going into the “Cell Properties” dialog again to remove the selections.

Note:	Once a document is protected, you cannot lock and hide cells, and so the Lock and Hide buttons are grayed out, as is the Attributes command from the Format menu.


Changing column width

Letting text overflow into the adjacent cell is fine as long as you don’t put information into that adjacent cell. However, in the body of most spreadsheets, you’ll want columns of information side by side, and if you do put information into the adjacent cell, the end of the long text is ‘clipped’.

To avoid this, you can alter the widths of the columns so that the texts will fit in them without leaking over into adjacent cells. You can change the width of a single column or several columns at once.

To change the width of a single column, position the mouse pointer over the right edge of the column bar. The mouse pointer will change to a movement indicator:

Drag the mouse (keep the mouse button pressed while moving) to resize the column.

To change several columns at once, first select them and resize any of the selected cells.

Alternatively, you can select the Column Width command from the Format menu. The “Column Width” dialog box is displayed.

Do one of the following:

•	In the Column Width box, type the width you require in points. A point is 1/96 of an inch, or 0.026cm, so type 96 if you want the column to be 1 inch.

•	You can ask Spreadsheet to find the best width, given the texts you have entered. To do this, click on the Best Fit button. Spreadsheet will then look at the longest text in the chosen column, and adjust the width so that the text will be displayed in its entirety. If you have highlighted several columns, Spreadsheet will find the appropriate best width for each of the columns.

•	If you want to go back to the default width of 80 points, click on the checkbox next to Standard.

When you have altered the column width to suit, click on the OK button, and the spreadsheet will be redrawn to your specification.

Note:	You can tell Spreadsheet to keep the width of a column, and instead of writing across into adjacent cells, move extra text down onto a new line within the same cell. To do this, select the cell in question and choose the Alignment command from the Format menu. Then click on the checkbox next to Wrap Text in the “Alignment” dialog box.

See also: Changing row height.


Changing row height

Ability will automatically adjust the height of a row to match the largest font you’ve used, so you won’t normally need to change row height.

To change the height of a single row, position the mouse pointer over the top edge of the row bar. The mouse pointer will change to a movement indicator. Drag the mouse (keep the mouse button pressed while moving) to resize the row.

To change several rows at once, first select them and resize any of the selected rows.

Alternatively, you can select the Row Height command from the Format menu. The “Row Height” dialog box is displayed.

Do one of the following:

•	In the Row Height box, type the height you require in points. A point is 1/96 of an inch, or 0.026cm, so type 48 if you want the row to be a half inch tall.

•	You can use Spreadsheet to find the best height, given the texts you have entered. To do this, click on the Best Fit button. Spreadsheet will then look at the highest text in the chosen row, and adjust the height so that the text will be displayed in its entirety. If you have highlighted several rows, Spreadsheet will find the appropriate best height for each of the columns.

•	Select the Standard checkbox to return to the default height.

When you have altered the row height to suit, click on the OK button, and the spreadsheet will be redrawn to your specification.

See also: Changing column width.


Copying and moving information

In a spreadsheet, you can copy or move information:

•	From one cell to another

•	From one range of cells to another range of cells

When you copy information, Spreadsheet creates duplicate information in the destination cells you specify, and the information also remains in the original cells.

Any formulas in the copy range are propagated in the destination range. For example, a reference to the cell A1 in a formula in cell A2 will become a reference to cell B1 if the formula is copied to cell B2. You can override propagation: see absolute addresses.

When you move information, Spreadsheet removes it from the original cells and displays it at the destination cells instead. When you move formulas, any cell references within the range are adjusted so they work in their new location. References made to cells outside the range are preserved.

In either case, if there is already information in the destination cells, Spreadsheet replaces it with the new information.

The most effective way of copying and moving information is using drag and drop.

You can also use cut and paste in a number of ways:

Copy and moving from one cell to another

Copy and moving from one range to another

Copying one cell to a range

Copying a range of cells to a single destination cell

Copying one range to another range

Note:	As Spreadsheet is a Windows program, anything you copy or cut is placed in the Clipboard. This means that you can paste it into another spreadsheet, or another type of document entirely, such as a database.


From one cell to another cell

To copy or move a single cell’s contents to a new location:

1.	Click on the “source cell” - the cell you want to copy.

2.	Select the Copy [image] button or the Copy command from the Edit menu. If you wish to move the information, use the Cut [image] button instead.

3.	Click on the “destination cell”.

4.	Select the Paste [image] button or the Paste command from the Edit menu.

See also:

Drag and drop

Copying and moving information


From one range to another range

You can also copy or move the contents of a range of cells from one place to another.

1.	Select the range of source cells.

2.	Select Copy [image] from either the toolbar, the Edit menu or by right clicking your mouse. If you wish to move the information, select Cut [image] instead of Copy.

3.	Select the range of destination cells, and then select Paste [image] from the toolbar, Edit menu or by right clicking your mouse.

Providing the copy range can fit exactly into the paste range (i.e. the paste range is an exact multiple of the copy range), you can copy information to a much larger area than the copy range. To do this select the paste range before pasting. The paste range will contain an exact multiple of the copy range. This is extremely important when building spreadsheets. For example, you can make a formula work for one column and then copy it, in one operation, to many columns.

See also:

Drag and drop

Copying and moving information


Copying one cell to many other cells

You can copy one cell and paste it into any number of destination cells. In the example below, a formula has been entered in cell C4, =C3*$A4, to convert January’s figure from £ to $:

You can copy the formula to make it work for February, March and April:

1.	Click on cell C4 to make it the active cell.

2.	Click the Copy button [image] on the toolbar.

3.	Select cells D4 through to F4 to mark the destination range.

4.	Click the Paste button [image] on the toolbar.

Note the use of the absolute reference in the formula – as you copy it to February, March and April, the reference to C3 changes to D3, E3, F3 respectively but they all still refer to A4 to get the exchange rate. See absolute addresses for more details.

The above operations can also be used to move the contents of one cell to many other cells, if Cut is used instead of Copy.

See also:

Drag and drop

Copying and moving information


Copying many cells to one

This saves you from having to select an area the same size as the copy range when pasting: just point to the top-left most cell in the destination range and Ability will fill in the rest. In the following example, a new line for DM has been added:

You can copy the formulas in row 4 and make them work for row 5:

1.	Select cells C4 through to F4.

2.	Click the Copy button [image] on the toolbar.

3.	Click on cell C5 to mark the top left corner of the destination range.

4.	Click the Paste button [image] on the toolbar to fill row 5 with formulae.

The above operations can also be used to move the contents of many cells to one cell, if Cut is used instead of Copy.

See also:

Drag and drop

Copying and moving information


Copying many cells to many other cells

The number of cells in the copy range must divide exactly into the paste range. In the following example, cells C4 and C5 have the correct formulas which need copying to D4 through to F4 and D5 through to F5.

1.	Select cells C4 through to C5.

2.	Click the Copy button [image] on the toolbar.

3.	Select cells D4 through to F5 to mark the destination range.

4.	Click the Paste button [image] on the toolbar to copy the formulae.

The above operations can also be used to move the contents of many cells to many other cells, if Cut is used instead of Copy.

See also:

Drag and drop

Copying and moving information


Drag and drop

"Drag and drop" is a technique, using your mouse, for moving, copying, filling in and deleting spreadsheet data. "Drag and drop" in Ability is supplementary to the more traditional copy, cut, paste, delete and fill commands, although easier to use once learnt.

See:

Drag and copy

Drag and move

Drag and fill

Drag and delete


Drag and move

To move the contents of a cell (or selected range):

1.	Click onto the cell you want to move.

2.	Point anywhere along the cell's border.

3.	When the mouse pointer changes to an arrow, hold down the left mouse button.

4.	Drag the cell outline to the target cell. Release the mouse button and Ctrl key.

The contents of the cell will be moved to the target cell. This has the same effect as cut and paste.

See also:

Drag and drop

Copying and moving information


Drag and copy

To copy the contents of a cell (or selected range):

1.	Click onto the cell you want to copy.

2.	Point anywhere along the cell's border.

3.	When the mouse pointer changes to an arrow, hold down the left mouse button and the Ctrl key.

4.	Drag the cell outline, while keeping the Ctrl key depressed, to the target cell. Release the mouse button and Ctrl key.

The contents of the cell will be copied to the target cell. This has the same effect as copy and paste.

See also:

Drag and drop

Copying and moving information


Drag and fill

You can drag and fill the contents of a single cell. or range of cells, to another range of cells. Certain types of ordered data, such as numbers, days and months, are incremented automatically. Follow these steps:

1.	Click into the source cell.

2.	Move the mouse pointer over the cell handle in the bottom right corner of the cell:

3.	When the mouse pointer turns to a cross, hold down the left mouse button and drag the cell outline till it covers the area you want to fill.

The contents of the source cell will fill the selected cells. Note that only cells adjacent to the source cell -either up, down, left or right - can be filled. To fill an area, rather than just a column or row range, follow steps 1 to 3 above then drag the range you've just created to fill the remaining area.

If the source cell contains a number or a text string with a number at its end, the selected cells will be filled incrementally. Here are some examples:

Source cell	Target range after drag and fill
1998	1999, 2000, 2001
Product1	Product2, Product3, Product4
Jan	Feb, Mar, Apr
Monday	Tuesday, Wednesday, Thursday
The last two examples show how the built-in ordered lists can be used- see Custom lists for more information.

Sometimes, when the source cell contains a number or member of a custom list, you may not want a range to be filled with incremental values. In these cases, you should first select the required range and then select the Fill command from the Edit menu or click on the Fill button on the toolbar. In the "Fill dialog", make sure you leave the Series box unselected and then click on OK. The contents of the source cell will be copied exactly (without incrementation) to each of the cells in the selected range.

See also:

Drag and drop

Copying and moving information


Filling cells automatically

You can automatically fill a whole range of cells with values. You might use this, for example, when you set up a spreadsheet where you want a column for every year since 1984. Column A would be 1984, column B would be 1985, and so on.

There are two types of fill: normal and series. In normal fill, the same value is put in each selected cell. In series fill, a different value (calculated in a specified increment from the starting value) is put in each cell.

To fill in a range automatically with a particular value using Normal Fill, first enter the value in a cell and then select the range you want to fill (click and drag the mouse across and/or down the spreadsheet to select a range, making sure the first cell in the selected range is the cell containing the value).

Then click on the Fill button [image] or select the Fill command from the Edit menu. The “Fill” dialog is displayed.

Click on OK, making sure you leave the Series box unselected. The range will be filled with the value of the active cell. With normal fill, the fill is performed in one direction at a time. If the range spreads over an area greater than a single row or column, then you need to repeat the operation, specifying a different fill direction, to fill the whole area. For instance, you might first fill an area down and then right. (See below)

To fill in a range automatically with an incremented value, using Series Fill, first select the range you want to fill and then select the Fill command from the Edit menu or click on the Fill button on the toolbar. Next select the Series checkbox by clicking on it. In the Start box enter the value from which you want to start. In the Step box enter the step size by which you want the value to be incremented. Note that Ability recognizes various incremental lists, including numbers, text that ends with a number, and the lists, such as days of the week, contained in Custom Lists (under Options from the Tools menu). If you leave the Step box empty, Spreadsheet will automatically use an increment of 1.

With either fill type, if the selected range spreads over an area greater than a single row or column, then you can choose in which direction you would like the fill to work.

Left	Fills the cells to the left of the active cell with its value
Right	Fills the cells to the right of the active cell with its value
Up	Fills the cells above the active cell with its value
Down	Fills the cells below the active cell with its value
Series Fill automatically fills the whole of an area, according to the selected direction. Normal Fill fills in only one direction at a time. So, for instance, to normal fill a selected area with a particular value, you might select Down and then select Right.

Note:	Fill will work with formulas as well as values. Filling with a formula works in the same way as copying a formula: each formula is propagated throughout the range. The formula  should be entered in the active cell, as for normal fill, before selecting the range to be filled. See Copying a formula.for more details.

When all is set, click on the OK button. The selected cells will be filled with the specified values.

Example 1

To fill the area A1..D10 with a particular value, say the number 1, do the following:

Enter 1 in A1 and then select the range A1..D10. Select Fill from the Edit menu or click on it on the toolbar. The "Fill" dialog will be displayed. Leave the Down button switched on and then select OK. The first column in the range will be filled. Next, select Fill again and this time switch on the Right option by clicking on it. The rest of the range will be filled.

Example 2

To fill the range A1..D10 with an incremented series of invoice numbers, where the first one is ABC45, the step is 2 and the range is to be filled along rows first, do the following:

Select the range to be filled. Select Fill from the Edit menu or by clicking on the toolbar. The "Fill" dialog will be displayed. Click on the Series checkbox to activate it. In the Start box put ABC45. In the Step box put 2. Select the Right option. Select OK. The range will be filled across ways in increments of 2, from ABC45 to ABC123.


Deleting and inserting cells, rows and columns

See:

Drag and delete - using the mouse to delete cells

Erasing the contents of a cell   - removing contents, formulas and formats

Inserting cells. rows and columns  - creating new cells, rows and columns

Deleting cells, rows and columns  - deleting and removing cells, rows and columns.


Drag and delete

To delete the contents of a range of cells:

1.	Select the range.

2.	Move the mouse pointer over the cell handle:

3.	When the pointer changes, press on the left hand mouse button and drag back across the range. The range will be grayed out as you drag.

4.	Release the mouse button when the range is totally gray. The contents of the range will be deleted.

See also:

Drag and drop

Copying and moving information


Erasing the contents of a cell

You can erase the contents of a cell. To do this, activate the cell you wish to delete (usually by clicking on it), and then press the del key. This will delete everything in the cell, including contents and format.

If you wish to delete particular features of the cell while leaving others intact, select the Clear command from the Edit menu. The “Clear” dialog will appear.

In this dialog, you are given five choices:

All	Erases everything from the cell.
Values	Erases the value from the cell, leaving formulas and formats intact.
Formulas	Erases only a formula from the cell - the result of the formula will be left intact.
Contents	Erases only the contents of the cell, that is values and formulas; anything else you put in this cell will take on the existing formatting.
Format	Erases only the formatting of the cell – the contents will be left intact.
You can also access Clear by right clicking on your mouse, although the option to clear formulas and values are not available in this way.

Select the option you require, and click on the OK button.


Inserting rows and columns

As you’re building a spreadsheet, you might discover that you need to insert new cells, columns or rows.

To insert cells, select the cells where you want the new cells to go. Next, select Cells from the Insert menu (or by right clicking your mouse). The "Insert Cells" dialog will appear. Choose whether you want the selected cells and their contents to be shifted down or right. Click on OK to insert the new cells.

To insert a row, click on the row bar to select where you want the row to appear. The whole of the row is selected, rather than a range. Select Cells from the Insert menu (or by right clicking your mouse). The new row will be inserted, and the selected row and all rows beneath the selected row will be shifted one row down. Note that the "Insert Cells" dialog does not appear when inserting rows.

To insert a column, click on the column bar to select where you want the column to appear. The whole of the column is selected, rather than a range. Select Cells from the Insert menu (or by right clicking your mouse). The new column will be inserted, and the selected column and all columns to the right of the selected column will be shifted one column to the right. Note that the "Insert Cells" dialog does not appear when inserting columns.

It is possible to insert several rows or columns at once by selecting the required number of row or column bars.

Note:	Spreadsheet readjusts formulas and range references to compensate for inserted rows and columns.


Deleting cells, rows and columns

Note:	When you delete cells, rows or columns, any information they contain is also deleted.

To delete a cell or cells, select the cell(s) you want to delete. Next, select Delete from the Edit menu (or by right clicking your mouse). The "Delete Cells" dialog will appear. Choose whether you want the deleted cells to be replaced with cells from the same column(s) or row(s). Click on OK to delete the selected cells and shift cells from the same column (up) or row (left).

To delete a row, click on the row bar to select the row you want to delete. The whole of the row is selected, rather than a range. Select Delete from the Edit menu (or by right clicking your mouse). The selected row will be deleted, and all the rows beneath the deleted row will be shifted one row up. Note that the "Delete Cells" dialog does not appear when deleting rows.

To delete a column, click on the column bar to select the column you want to delete. The whole of the column is selected, rather than a range. Select Delete from the Edit menu (or by right clicking your mouse). The selected column will be deleted, and all columns to the right of the deleted column will be shifted one column to the left. Note that the "Delete Cells" dialog does not appear when deleting columns.

It is possible to delete several rows or columns at once by selecting the required number of row or column bars.

Note:	Spreadsheet readjusts formulas and range references to compensate for inserted rows and columns.


Using formulas

This is the real heart of the spreadsheet. You can use formulas to add, subtract, multiply, and divide the numbers in your spreadsheet. For example, you can:

•	Add up a column of numbers.

•	Automatically compute averages.

•	Solve mathematical problems like 3456.87 x (345.2498 + 1224.1 + 45.3)

•	Use any of Ability's built-in functions.

With formulas, you can design spreadsheets that help you explore the “what ifs”. What happens to your profits if expenses creep up 9%? What if the payments on a new car stretch over 48 months instead of over 24? This “what if” analysis is a vital component of business and financial planning.

See:

What is a formula?

How to enter a formula

Clearing formulas

Automatic totals

Function reference


What is a formula?

A formula is like a set of instructions for calculating or retrieving values. Here are some examples of formulas:

=341 * .065	Multiplies 341 by .065.
=A9 - C14	Subtracts the value in C14 from the value in A9.
=(A3 + A4)/.125	Adds the values in A3 and A4, then divides by .125.
=SALES - B5	Subtracts the value in B5 from the value in a field named SALES (you’ll read about naming fields later in this chapter).
=TOTAL(D8..D11)	Using one of Ability’s built-in functions (TOTAL), adds the values in fields D8 to D11.
See also:

Arithmetic operators

How to enter a formula

Function reference


Arithmetic operators

You can use formulas to add, subtract, multiply and divide numbers. You should use the following standard arithmetic operators in your formulas:

/	division
*	multiplication
+	addition
-	subtraction
Note:	These arithmetic operators are listed in the natural order of priority, from highest (calculated first) to lowest (calculated last). You can use parentheses to group information and to control the order of calculation.

In addition, you can use these logical operators:

%	the percentage sign simply divides a number by 100. For example, 25% means 0.25.
( )	parentheses group parts of a formula and control the order in which calculations are performed. For example, A2 * (B3 + C4) tells Spreadsheet to add B3 and C4 first, and then multiply the result by A2. Without the brackets, Ability would multiply A2 by B3 before adding C4.
^	the caret raises a number to a power (exponentiation). For example, 3^2 raises 3 to the power of 2, or squares it. You can also raise numbers to fractional and negative values. For example, 3^0.5 or 3^(1/2) finds the square root of 3 and 10^-2 gives the result of 1/(10*10) which is 0.01.


How to enter a formula

You build a formula by making reference to other cells. For instance, to display the total of two cell values in a third cell, you would put the formula (location of cell 1) + (location of cell 2) in the third cell. When the contents of cell 1 or cell 2 changes, cell 3 is updated to reflect the change.

Formulas are entered into the active cell just as you enter text or values. However, you always start a formula with an equals sign (=).

Note:	You can begin a formula with other operators if you like, for example “+” or "-".

Now you’re ready to build the formula. Lets use an example of adding cell A1 and A2 together and putting the result in cell A3. First, click on A3 and type “=”. Next, click on cell A1, type “+” and then click on cell A2. Note that the formula bar will read:

=A1+A2

You can type the entire formula directly if you want. Often though, you’ll find it easier to refer to a cell in a formula by simply clicking on it.

When you have finished the formula, click on the Confirm button [image] on the status bar or press enter, and the results of the calculation will be inserted into the active cell.

If you make mistakes while you’re building a formula and you want to start over again, press esc or click on the Cancel button [image] on the formula bar. Spreadsheet erases the formula and you can start again by entering an equals sign.

If you want cells to show their formulas instead of the results, open the View menu and click on the Formulas command. When formulas are showing, this command has a check next to it. To show results again, click on the command to remove the check.

See also:

Copying a formula

Absolute addresses


Copying a formula

You can often save time by copying a formula from one cell to another.

To do this, activate the cell whose formula you wish to copy. Then select Copy either from the toolbar [image], the Edit menu or by right clicking your mouse.

Next, click on the cell to which you wish to copy the formula, and select Paste either from the toolbar [image], the Edit menu or by right clicking your mouse. The formula is copied into the destination cell.

However, you might notice that the formula has been altered slightly. This is because Spreadsheet automatically adjusts the cell addresses in the formula so that they work logically in the new location.

To give an example, let’s say you entered the formula B8*C8 in cell D8. If you then copied it to cell D9, Spreadsheet would turn it into B9*C9, and in cell D10 it would become B10*C10.

If you do not want the cell addresses to be adjusted in this way, you need to specify them as absolute addresses .


Absolute addresses

As just described, when you copy formulas, Spreadsheet normally adjusts the cell addresses so the formula works in the new locations. Occasionally, though, you might not want the cell addresses to change.

You might want to copy a formula like B8*C8 to a new location in a spreadsheet and still have the formula multiply the value in B8 by the value in C8. You don’t want the formula to adjust the field addresses for the new location. In other words, you want B8 and C8 to be absolute field addresses in the formula.

To include absolute field addresses (as opposed to adjustable addresses) in a formula, precede the field address with a dollar sign ($). The dollar sign tells Spreadsheet to copy the formula exactly, and to leave the field addresses unchanged.

You can use a dollar sign to make:

•	absolute row addresses, e.g. B$8

•	absolute column addresses, e.g. $B8

•	absolute row and column addresses, e.g. $B$8

See also:

What is a formula?


Clearing formulas

Once you have entered a formula, you might decide that you do not want the cell to contain the formula any more, but simply to have the resultant value as its contents. This then protects the contents from change (when, for instance, one of the other cells referred to in the formula changes).

To turn a current formula result into a static cell value, select the cell whose formula you wish to clear. Then choose the Clear command from the Edit menu and select Formulas. This indicates to Spreadsheet that you want to clear the formula from the cell but without deleting the result. Click on the OK button.

You can clear the formulas in a range by first selecting the range and then repeating the above operation.

This procedure is irreversible (i.e. you cannot later turn the values back into the formulas which created them), so use it with care.

Note:	Clearing formulas also clears any links, turning them into static values.

See also:

How to enter a formula


Automatic totals

Auto-sum is a quick method of summing values in a column, row or range.

To use auto-sum, do one of the following:

•	Click into a cell below or to the right of the column or row you want to sum and select the Sum button [image]. Ability will show you the range about to be summed - accept this with the Confirm button, or press the Enter key. The SUM function will be pasted in the cell.

•	Select a range of cells and click the Sum button - Ability will fill-in row and/or column totals in the appropriate place.

Example 1 - totaling a column of figures

Say A1 though A10 contain numbers you want to total. Click into cell A11 (or A12 if you prefer to leave a gap) and click the Sum button. Ability will outline the range waiting for you to press Enter or click the Confirm button.

Example 2 - totaling a column of figures

As an alternative to Example 1, again say A1 though A10 contain numbers you want to total. This time select A1 through A10. Click the Sum button - Ability will put the total in cell A11. If instead you select cells A1 through A12, the total will be placed in A12 (that is to say the total will always be placed in the last, or last+1, cell of the selected range).

Example 3 - including row and column totals in one go

Suppose A1 through D5 contain values and you want row and column totals and a grand total. First select the cells A1 through D5. Click the Sum button. All the totals will appear below and to the right of the range with a grand total in cell E6.

See also:

Calculating current selection


Calculating current selection

Sometimes it is useful to see the results of calculations as you work in a spreadsheet but without having these results inserted into the spreadsheet itself. To this end Ability provides a status bar box called Autocalc, in which calculations can be performed automatically on any range of values that is currently selected.

To activate Autocalc right click on the right-most box on the status bar. A menu will appear with functions for Count, Sum, Average, Max and Min. Click on the function you require. This function will be active until you choose another one. Select the range of cells to which you want to apply the function. The result of the calculation will appear in the Autocalc box. To apply another function right click on the box again and click on another function to make it the active one. To switch off Autocalc click on None.

For example, to perform the full set of automatic calculations on values in the range A1..D10, you should first select the range and then activate Autocalc. Selecting the available functions one by one will tell you how many non-empty cells there are in the range and the sum, average, maximum and minimum of the values. To apply the currently selected function to another range, just select that range.


Turning recalculation on and off

Spreadsheet has an auto-calculation feature. This feature is turned on by default so Spreadsheet automatically calculates the results of all formulas after each entry you make that affects a formula.

To turn off auto-calculation, select the Auto Calculate command from the Tools menu: the check next to the command in the menu will be removed. Select the Auto Calculate command again to turn it back on.

When auto-calculation is disabled, you need to select the Calculate Now command from the Tools menu to calculate the results of formulas. Manual calculation of this sort is useful if you have many formulas or are putting in a lot of data, and you do not want to wait for Spreadsheet to recalculate after every entry that affects a formula.

Auto Calculate and Calculate Now operate on the current spreadsheet as a whole and not just on a selected cell or range.


Naming cells

Cells and ranges of cells can be assigned names. After a cell is named, you can use the name, instead of the cell address, in formulas and other references to that cell.

For example, supplies might be one expense category and utilities another. By giving names to the grand total cells for both supplies and utilities, you can build a formula that looks like this:

=SUPPLIES+UTILITIES

In addition, you might find it easier to remember SUPPLIES than a precise cell address. So, if you want to use the Go To command from the Edit menu to move the cursor to that cell, you can simply enter the name SUPPLIES rather than a cell address.

See:

Assigning names

Naming a range of cells


Assigning names

To name a cell, activate it (usually by clicking on it), and then select the Range Name command from the Insert menu. The “Range Name” dialog is displayed.

Type in a name for the cell in the Name box. The existing cell and range names are listed below. Notice how the addresses of named cells and ranges are given in square brackets after the names. Click on the Add button, and the cell name will be remembered. Select Close to save the new name.

If you want to name a range, the steps are the same as for a cell, only make sure you select the range before selecting the Range Name command (although it is possible to type in the range directly).

The name you have chosen will appear in the Cell Index box on the formula bar.

To refer to named cells in formulas, just use the name wherever you would otherwise use the cell address.

See also:

Changing or removing names

Naming a range of cells


Changing or removing names

You can easily change or remove a name assigned to a cell or range. Click on the cell to activate it or select the range, then choose the Range Name command from the Insert menu. In the “Range Name” dialog, use the Delete and Delete All buttons to delete as required. You can select and delete the named cells and ranges at will.

To rename a cell or range, click on it in the Ranges box in the Range Name dialog, and then click on the Name box. Delete the name by selecting it and then either using Delete on the keyboard or right clicking on your mouse and selecting Cut. Type in the new name and select the Add button. The new cell or range name will appear in the Ranges box. Note that the old name, which is now inactive, is still there. To erase this, select it and click on the Delete button.

See also:

Naming a range of cells


Naming a range of cells

A range of cells can also have a name. If you name a range of cells, you can refer to all the cells in the range by entering the single range name. For example, you can use a named range in a formula.

To name a range, select the cells in the range using either a mouse or the cursor keys. Then choose the Range Name command from the Insert menu. The “Range Name” dialog is displayed.

Type in a name for the range. The existing range names are listed in the Ranges box below. Notice how the address of the cells in the range are given in square brackets after the name. Click on the Add button, and the range name will be remembered. Select Close to exit the dialog.

To refer to named cell ranges in formulas, just use the name wherever you would otherwise use the cell addresses. For example, if you name the range A1 to A10 as MYLIST, these two formulae are equivalent:

=TOTAL(MYLIST)
=TOTAL(A1..A10)

See also:

Changing or removing names


Sorting

You can sort the fields of your spreadsheet. Sorting is a way to re-arrange items in a specific order, according to the contents of a key row or column within the selection.

To sort rows and columns in a spreadsheet, first shade the range to be sorted. Then select the Sort command from the Tools menu. The “Sort” dialog box is displayed.

In the Sort box, you can choose between sorting by Rows and sorting by Columns.

In the three boxes below the Sort box, you can specify up to three sort levels. For each sort you should specify which column to sort by when sorting rows, or which row to sort by when sorting columns.

For each sort, you can also choose between Ascending order (where normal ascending numerical or alphabetical order is applied) and Descending order (where normal descending order is applied). Normal sort order can be overridden by selecting a Custom List from the Order box. This enables you to sort columns or rows that contain, for example, days of the week or months, or any lists that you may have added to Custom List yourself.

If you apply more than one sort, columns or rows with duplicates in the first sort level are sorted according to the second and third sort levels. If you have not specified second and third sort levels, rows or columns with duplicates in the first sort are left in the order in which they are found.

To sort on more than three levels, use the Sort command several times using the least significant keys first.

Note that when a sort is applied using Normal order, the following criteria apply:

•	numbers are sorted according to their numerical order, words according to their alphabetical order;

•	numbers come before letters (3 before a; 3a before a3);

•	a lower case letter comes before its upper case equivalent (if you want letters to be ordered without regard for case, then check the Ignore Case box by clicking on it).


Searching

You can search a spreadsheet for a cell that contains a particular text string, value, or result of a formula. To begin a search, click the Find button [image] on the toolbar or select the Find command from the Edit menu. The “Find” dialog box is displayed.

In the Find what box, type the text, value or result of a formula you wish to find, or paste it in from the clipboard.

In the Where box select Values if you only want to find text or a value; select Formulas if you only want to find the result of a formula; select Both if you want to find either text/value or a result of a formula. For instance, if you enter 53 in the Find what box and if cell A10 contains the value 53 and cell A11 contains 53 as the result of a formula =21 + 33, then setting Values will find A10 but not A11, setting Formulas will find A11 but not A10, and setting Both will find both A10 and A11.

In the Direction and Scope boxes, choose where you would like to look for the item:

All	Search from the beginning of the spreadsheet or selection
Down	Search from the active cell to the end of the spreadsheet or selection
Up	Search from active cell to the beginning of the spreadsheet or selection
Whole document	Search the whole document
Selection	Search only the selected cells. Use this option to search a specific row or column by first selecting a row or column or to search a selected area
Select the Match Case checkbox to find only those occurrences with the combination of uppercase and lowercase letters specified in the Find what box.

Select the Match whole words only checkbox to find occurrences that are words by themselves, and not part of a larger word.

When everything is set, click on the Find Next button, and Ability will find the next occurrence of the text, value or result of formula as specified. If no matches are found, a message will tell you that the search was unsuccessful.


Finding next occurrence

You can repeat the last performed search by selecting the Find Next command from the Edit menu. Since Ability remembers what you last searched for, you do not have to go back into the Find dialog to set up a search.


Finding a formula

If you want to find a formula, then select Formulas from the View menu first. The spreadsheet will display formulas rather than their results. You can then use Find to search for formulas directly. Note that when formulas are displayed they are treated as values for the purposes of Find and so the Where box should be set to Values.


Replacing

To replace a particular text string, value or result of formula with another one, click on the Replace button [image] on the toolbar or select the Replace command from the Edit menu. The “Replace” dialog box is displayed.

This is very similar to the “Find” dialog box. However, it has some additional sections.

In the Find what box, type the text, value or result of a formula you wish to find, or paste it in from the clipboard.

In the Where box select Values if you only want to find and replace text or values; select Formulas if you only want to find and replace the result of a formula; select Both if you want to find and replace either text/values or the result of a formula. For instance, if you enter 53 in the Find what box and if cell A10 contains the value 53 and cell A11 contains 53 as the result of a formula =21 + 33, then setting Values will find A10 but not A11, setting Formulas will find A11 but not A10, and setting Both will find both A10 and A11.

In the Replace with box, type the text, value or formula you want to replace the found item (or paste it in from the clipboard). Note that you can replace a value or text with a formula, or an existing formula with another, and vice versa.

In the Direction and Scope boxes, choose where you would like to look for and replace the text, value or result of a formula:

All	Search from the beginning of the spreadsheet or selection
Down	Search from the active cell to the end of the spreadsheet or selection
Up	Search from active cell to the beginning of the spreadsheet or selection
Whole document	Search the whole document
Selection	Search only the selected cells. Use this option to search a specific row or column by first selecting a row or column or to search a selected area
Select the Match Case checkbox to find only those occurrences with the combination of uppercase and lowercase letters specified in the Find what box.

Select the Match whole words only checkbox to find occurrences that are words by themselves, and not part of a larger word.

When everything is set, click on the Find Next button, and Ability will find the next occurrence of the text, value or result of formula as specified. If no matches are found, a message will tell you that the search was unsuccessful.

Since Find Next finds values one by one you can choose to replace these as you go along by clicking on the Replace button. Ability will then automatically replace the present occurrence and proceed to search for the next one. If you choose not to replace a particular occurrence, click on Find Next instead. Alternatively, click on the Replace All button to replace all occurrences of the find item with the replace item.


Replace next occurrence

You can repeat the last performed replacement by selecting the Replace Next command from the Edit menu. Since Ability remembers what you last entered in the Replace dialog, you do not have to go back into the dialog to set up a new search and replace.


Transposing ranges

You can transpose selected cells in a spreadsheet. Transposing is a way to rotate the cells in a spreadsheet from horizontal to vertical, with the top row becoming the first column; the second row, the second column; and so on.

Transposing cells in a spreadsheet can be useful when you are including data in a document; for instance, what appears in rows of a spreadsheet might be more appropriately displayed as columns in a report.

To transpose an area of the spreadsheet, select the range of cells you wish to transpose. Then select Cut from the toolbar or the Edit menu, or by right clicking your mouse. You can use the Copy command instead if you wish to keep the original.

Select the range in which you wish the transposed values to be put, and then select the Paste Special command from the Edit menu. In the “Paste Special” dialog, click on the checkbox next to Transpose. Click on the Paste button, and the spreadsheet will be redrawn with the data transposed data.


Transforming ranges

You can transform selected cells in a spreadsheet. Transforming is a way of adding, subtracting, multiplying or dividing two ranges.

To transform an area of the spreadsheet - the target range- first type in and select the range of cells you wish to use to effect the transformation. Then select Copy from the toolbar (or the Edit menu, or by right clicking your mouse).

Next, select the target range and then select the Paste Special command from the Edit menu (or by right clicking your mouse). In the Operation group box, click on either the Add, Subtract, Multiply or Divide buttons. Click on the Paste button and the spreadsheet will be redrawn with the data transformed.

Example 1

The target range contains 1, 2, 3, 4, 5, which you want to multiply by 2. Enter 2 into a spreadsheet cell and Copy this cell. Select the target range and then select Paste Special. Select Multiply and then click on the Paste button. Each of the target cells will be multiplied by 2 to give 2, 4, 6, 8, 10.

Example 2

The target range contains 25, 20, 15, 10, 5, which you want to divide by 5, 4, 3, 2, 1, the transformation range. Type in and select the transformation range. Copy this range and then select the target range. Select Paste Special and click on Divide. Click on the Paste button. The values in the target range will be divided by the corresponding values in the transformation range to give 5, 5, 5, 5, 5.


Linking

You can link a field in a Spreadsheet to another field in:

•	the same spreadsheet

•	a different spreadsheet

•	a Write document or Database

Linking is a way to tie information in one field to information contained in another. When you link fields, if you change the value in one field, the value in the other field automatically changes, too.

Links can be either one way or two way:

•	A one way link, at its simplest, is just a cell reference for example =A1 entered in cell A5 creates a one way link - you can make changes to A1 that will reflect in A5 but not the other way round. For more details, see one way links.

•	A two way link is similar but the data can be changed at either end. A logical consequence of this is that two way links cannot be used in building a formula (unlike one way links). For details on how to create and use two links, see two way or hot links.

One way and two way links can work between documents: a spreadsheet can have a one or two way link to another spreadsheet or Write document. See linking documents through fields.


One way links

The easiest way to set up a one way link is to type it directly into the destination cell. For example, if A1 contains 123, type the formula:

=A1

in any other cell, say E10. The contents of E10 will always be the same as A1. This is identical to a standard spreadsheet formula, so =A1 + 10 is also valid.

As an alternative, you can use a paste special operation to achieve the same result. For example:

1.	Click in cell A1

2.	Select Copy by right clicking your mouse

3.	Click in cell E10, which is to contain the linking expression

4.	Select Paste Special by right clicking your mouse

5.	Select Paste Link from the "Paste Special" dialog

See also:

One way links between documents

Two way or hot links


Two way or hot links

The easiest way to create a two way link is to type it directly. For example, if A1 contains 123, type the formula:

! A1

in any other cell, say E10. The contents of E10 will always be the same as A1 and you can edit data at either end of the link. For example, entering 456 in E10 or A1 will cause both E10 and A1 to display the same result - 456.

As an alternative, you can use a paste special operation to achieve the same. For example:

1.	Click in cell A1

2.	Select Copy by right clicking your mouse

3.	Click in cell E10, which is to contain the linking expression

4.	Select Paste Special by right clicking your mouse

5.	Select Paste Hot Link from the "Paste Special" dialog

To convert a one way link into a two way link, select the Edit Source button [image] or select the Attributes command from the Format menu and then check Edit Formula Source. Selecting the Edit Source button again will convert the two way link back into a one way link.

See also:

Two way links between documents

One way links


Page set-up

Whenever you open a new file in Spreadsheet, it is given a default set of margins. To change these defaults for the current spreadsheet, select the Page Setup command from the File menu. The “Page Setup” dialog box is displayed:

Here’s how the different Margin and settings affect your page:

As the diagram above shows, all the measurement are from the edge of the page.

The header and footer sits within the top and bottom margin. So if your top margin is too small, you won't see all your header text. To control the margins for header and footer, select Header & Footer from the Format menu. Enter a value in the From edge box in the header and footer tabs.

If you want to set new default margins for all subsequent spreadsheets then select Options from the Tools menu and click on the Page Margins, Header and Footer tabs. The new default settings will not affect existing spreadsheets or the current one if its settings have already been altered.

Note:	To change the units of measurement used, select the Options command from the Tools menu and click on General. You have a choice between S.I. and Imperial units.


Headers and footers

You can add information, such as a spreadsheet title and page number, that will appear at the top and bottom of every page when you print the spreadsheet. Text that appears at the top of every page is called a header; text at the bottom is called a footer.

To print the header, you need to leave enough space between the top of the spreadsheet and the top of the page so that the header text can fit into it, bearing in mind the fonts you have used. The same applies to footers. The defaults Ability uses will fit most header and footer definitions. You can fine tune these dimensions in the "Header and Footer" dialog.

See:

Adding a header and footer


Adding a header and footer

To add a header and/or footer, select the Header & Footer command from the Format menu. The “Header & Footer” dialog appears. To create a header click on the Header button in the dialog; to create a footer, click on the Footer button.

The “Header & Footer” dialog contains three boxes to set up the header and footer information. You can type any text you like into these boxes. Text in the Left box will start printing at the left margin, text in the Right box will end at the right margin, and text in the Center box will appear in the middle.

Use the Font button to set the font for each individual header or footer area.

You can have Ability insert information automatically. Position the cursor in the box where you want the text to appear and then:

Click on this button...	To...	Using this code...
Page Number	Insert the current page number at print time.	&P
File	Insert the name of the spreadsheet file.	&F
Time	Insert the time the spreadsheet was printed.	&T
Date	Insert the date the spreadsheet was printed.	&D
When you select one of these buttons, Ability inserts a code in the dialog box instead of the actual text (since the text changes depending on the current time and date and the number of pages and name of the spreadsheet).

You can delete the codes (or add them by typing them directly) in the same way as you can edit any other text.

If you want to change the default margin between the top and bottom edges of the paper and the header and footer respectively, then click on the From edge box, delete the default setting and type in the new margin in each case as required. This will change the settings for the current spreadsheet.

If you want to set a new default for all spreadsheets then select Options from the Tools menu. Click on the Headers or Footers button in the "Options" dialog. Change the default From Edge setting as required. Note that this changes the setting for all subsequent spreadsheets but not for already existing spreadsheets or the current one if it has already been altered in the Headers & Footers dialog under the Format menu.

The First Page box tells Spreadsheet from which page it should begin printing the headers and footers. The default is 1, which you should replace as required.

If you want to create a footer as well as a header, then click on the Footer button and create the footer in the same way as you created the header, before selecting OK to save the new settings.


Freezing sections of a spreadsheet

As you fill more and more rows of a spreadsheet, the top rows move up and out of view. Therefore, if you've put titles in a row, they move up out of view too. Similarly if you've put titles in a column, they move out of view as you fill more and more columns to the right.

You can use the Freeze command to freeze particular rows, columns and ranges. When you freeze a spreadsheet, the frozen part remains visible on the screen as the rest of the spreadsheet moves out of view.

To freeze a row or several rows:

1.	Select the rows using the row bars to the left of the row.

2.	Select the Freeze command from the Window menu.

The frozen rows will move to the top of the screen and stay visible no matter where you are in the spreadsheet. You can freeze columns in the same way by first selecting the columns you want to freeze.

To freeze rows and columns in one go, first select the intersection of the rows and columns to be frozen. For example, suppose you want to freeze the first three rows and columns A and B, follow these steps:

1.	Select the range A1..B3

2.	Select the Freeze command from the Window menu.

Note that you can exclude rows and or columns from displaying at all by excluding them from the selection. In the above example, if the range A2..B3 were selected instead, row 1 would  not be visible.

Also note that the effects of freeze will go through to print time as well - you can use it to make sure each page has the same row and or column repeated at the top or left side.

To turn off the freeze, select the Unfreeze command from the Window menu.


Splitting a spreadsheet

It is possible to split the spreadsheet screen into vertical or horizontal sections. These sections are different views of the same spreadsheet and not separate spreadsheets. The advantage of this is that when a spreadsheet contains a large amount of data, you can view data that would normally be off screen in one section, while working with the data of your choice in another section.

To split the screen horizontally, depress and hold down the left mouse button over the bar attached to the vertical scrollbar. A horizontal axis will be displayed. Move this to where you want the screen to be split and release the mouse button.

To split the screen vertically, depress and hold down the left mouse button over the bar attached to the horizontal scrollbar. A vertical axis will be displayed. Move this to where you want the screen to be split and release the mouse button.

Both horizontal and vertical splits can be applied at the same time to the one spreadsheet.

Scroll through the views as required, till you have located the data you want to remain visible on screen while you are working. You can choose to work in any or all of the sections, since anything you add, edit or delete in a particular section applies to the whole spreadsheet.

To clear a horizontal or vertical split, just double click on one or both of the axes as appropriate.

You can also use the Split command from the Window menu to split the screen.


Printing

You can print the whole of the document you are working on, or only certain pages. You can also print to a file rather than to paper (perhaps for taking to a professional print bureau).

See:

Printing the spreadsheet

Print preview

Print set-up


Print preview

Before starting to print, you might like to see how your document is going to look on paper. To do this, click on the Print Preview button [image] on the toolbar or select it from the File menu.

The display switches to a print preview mode, in which the document is shown at a reduced size, to give you an overall view.


Print set-up

To choose which printer, page size and orientation you want to use and which bin you want the paper to feed from, select the Print Setup command from the File menu. The “Print Setup” dialog box is displayed.


Printing the spreadsheet

To print the spreadsheet, click on the Print button [image] on the toolbar or select the Print command from the File menu. The “Print” dialog box displays:

The active printer is displayed at the top of the dialog box. To change this click on the arrow at the right of the Name box and scroll through till you find the printer you want.

In the Print range group box, you specify how much of the spreadsheet you want to print:

All	Prints the entire spreadsheet.

Pages	Prints the range of pages you specify in the from and to boxes.

Selection	Prints the selected cells only.

Check the Print To file box to create a new file on a disk, instead of sending the document to the printer. You will be prompted to give the file a name.

Type the number of copies you require in the Number of copies box and leave the Collate box checked if you want the pages to printed in sequence (rather than all page 1's then all page 2's and so on).

If you want to select further properties click on the Properties button. The properties are printer dependent and so vary from printer to printer.

When all is set, click on the OK button and the printing process will commence. There will be a pause while the information is sent to the printer, and then the physical printing will begin. Ability will divide your spreadsheet into pages left to right and top to bottom. If you find, for example, that page 2 and 4 print out with a single column and you really wanted to fit the entire spreadsheet onto two pages, either reduce the width of one or more columns or reduce the left and right margin (and then check with print preview).

Note:	If you don’t want the grid to print out, select the Grid command from the View menu to turn the grid off prior to printing.


Spell-checker and thesaurus

The Spell-checker and Thesaurus are both available on from the Tools menu. They operate in Spreadsheet in the same way as they do in Write. See Spell-checker and thesaurus in Write for more details.

To change the way spell checker and thesaurus work, select the Options command from the Tools menu and then select Spelling & Thesaurus.

Click on the checkbox next to Always suggest to ensure that the spell-checker always suggests alternative spellings for words it does not recognize.

Click on the checkbox next to Use only main dictionary if you do not want the spell-checker to take account of any words that you might have previously added to the dictionary yourself. You will usually want to leave this box unchecked. The main dictionary is the original dictionary that is supplied with Ability.

The User Dictionary contains any words you might have added yourself and is kept separate from the main dictionary. This can be edited by clicking on the button to the right of the box and then opening up the file from the Open dialog that appears.

Language is the form of English used in the dictionary i.e. American or British English.

You can choose to have automatic spell checking by clicking on the Auto recognize spell errors box. This works as you type and immediately tells you if the dictionary does not recognize a word by showing the word in the color you have chosen from the Spell error text color box. See Autospell for more details.


Autospell

Autospell is a quick way of automatically checking a spreadsheet document for spelling mistakes, as you work, and correcting these on the spot.

To activate Autospell right click on the box second from the right on the status bar. Clicking on Auto Recognize will cause Ability to display in red any words that it does not recognize. To add an unrecognized word to the User Dictionary or to examine suggested corrections, you should first select the cell containing the word and then right click on the Autospell box again. The Add option adds the word to the dictionary. If Ability can find any suggestions, these will be displayed at the top of the Autospell menu. Click on one of these if you want it to replace the word in red. Auto Recognize will stay switched on until you switch it off.

You can also check for spelling mistakes by using the Spelling option. This goes through your document searching for errors and brings up a dialog whenever an error is found. Here you are able to change or ignore the word or add it to the dictionary. Spelling operates only when you select it and is closed down when you exit the dialog.


Default settings

Whenever a new spreadsheet is created, certain defaults are applied. For example, the font might be 9pt Arial and the background color white. Although you can always change these for individual spreadsheets, it's useful to be able to set them for all new spreadsheets as well.

Select the Options command from the Tools menu. The “Options” dialog box is displayed. In most cases, each tab of this dialog works in a similar way as the equivalent command from the Format menu - but as well as changing the current spreadsheet, the global default for all new spreadsheets are set.

Select from:

•	General options - spreadsheet size, units of measurement, trailing/leading formats.

•	Font - see the font command.

•	Background Color - see the color command.

•	Header and Footer - see headers and footers.

•	Margins - see page margins.

•	Spell options - see spell checker and thesaurus.

•	Custom lists.


General options

To modify the General page of the Options tab, select the Options command from the Tools menu and then select General.

Set any of the following as defaults for all new spreadsheets (as well as the current one):

•	Spreadsheet size (the maximum setting is 256 columns and 65536 rows)

•	Column width and row height.

•	The list of leading and trailing characters e.g. currency symbols.

•	Zoom, view grid and view page breaks.

•	Unit of measurement, Autocalc and Undo.

See also, other default settings.


Custom lists

To modify the Custom Lists page of the Options tab, select the Options command from the Tools menu and then select Custom Lists.

Ordered lists are typically days of the week or months, and are automatically filled in on drag-copy of a cell containing a member item. For example, enter "Jan" in cell A1 and use drag-copy to fill A2 to A12 with the remaining months.

•	In the Custom Lists box, select an ordered list for editing or removal. Use NEW LIST to create new custom lists.

•	The List Entries box displays individual items from the current ordered list. You can edit the items in this list, or add new ones (use Ctrl-Enter to create new line). To save the modifications, click the Modify button.

•	To create a new list, make sure NEW LIST is selected in custom lists, add some items in List Entries and click the Add button.

•	To remove a list select it in the Custom Lists box and click on the Remove button.

See also, other default settings.


Chart

Chart lets you plot a series of numbers from a spreadsheet. There are a wide range of chart styles to choose from, multiple series can be plotted and 3D views created.

The resulting chart can be saved in a separate window, attached to the spreadsheet, or be inserted into the middle of a spreadsheet. A single spreadsheet can contain, or have attached, many different charts.

You can insert charts into Write documents and, since Spreadsheet can display information from database tables, chart information from databases.

See:

Creating a chart

Editing a chart

Deleting a chart

Printing a chart


Creating a chart

For example, the following data, shows the output of raw materials over four months and includes both row and column titles.

To create a chart from this data:

1.	Select the range A1..E4.

2.	Select Chart from the Insert menu.

3.	Choose On This View to insert the chart into the current spreadsheet or As New View to create a new window that is attached to the spreadsheet - see attached charts for more details.

4.	Follow the steps in the chart wizard.

With the chart wizard, you can click on the Finish button at any point (you can always come back and edit all the options later). You'll probably want to set the style at least though. The chart wizard will prompt you for the following:

•	Style - choose from bar chart, pie, x/y plot and so on.

•	Data range  - in this case A1..E4, does the series run down with the data points across or visa-versa.

•	Titles and legend display.

•	Appearance - the background color and 3D display.

Once created, you can modify any of these options, using the Chart menu, and further modify the way the axes are drawn and each series is displayed.


Style

To change the style of chart, whether you want to display a pie, bar, or other type of chart, select the Style command from the Chart menu. (Double-click on a chart if you can't see this menu).

•	Gallery. Select a chart type - for a brief description of each chart type, see gallery of chart styles

•	Gridlines. Whether to display grid lines or not. Choose from None, Horizontal, Vertical or Both.

•	Color scheme: set this to Solid, Black and White patterns, or Color patterns.

•	Stacked: set this to Stack to add the values of the data points together for each series and plot one bar per series; set to Stack 100% to display the stack as a percentage bar. This can only be used with Columns and Horizontal Bars.

•	Point type: set this to a point style of your choice from the available options.


Gallery of chart styles

Here's a brief description of each chart style:

Area	Joins the data points in a series with a line and shades the area under this line.
Columns	Plots a column for each data point in a series.
Cubes	Plots a cube for each data point in a series.
Lines	Plots a point for each data point in a series and connects the series with a line.
Marks	Plots a mark for each data point in a series. You can choose the type of the mark in Point type.
Fit to Curve	Plots a point for each data point in a series and connects the series with a curve.
Horizontal bars	Plots a horizontal bar for each data point in a series.
Scatter (X vs. Y)	Plots a set of points using a column (or row) of values as the X-values and the values from another column (or row) as the Y-values. More on scatter charts.
(Open) Hi-Low-Close	Plots the extent of the range of values in a series, where the values plotted are the highest, the lowest and the closing value, and, optionally, the opening value. The values must be entered in a column (or row) in this order: Low-(Open)-Close-High. More on Hi-Low charts.
Pie	Plots the percentage values for the data points in a series, using a pie chart. If there is more than one series, only the first series is charted.
Doughnut	Plots the percentage values for the data points in a series, using a hollowed out pie chart or doughnut. If there is more than one series, only the first series is charted.
Polar	Plots a point for each data point in a series along an edge terminating at a pole, where each data point in the series has its own edge.
Surface	Plots a point for each data point in a series, where there is more than one series, joins each series with a line, and shades the area between the lines to produce a surface.
Pareto	Plots a column for each data point in a series and a mark showing the cumulative percentage for each data point when compared to the total for the series. The marks are joined by a line. If there is more than one series, then only the first series is charted.
For details on how to change chart style, see changing chart style.


More on scatter charts

For example, you wish to chart the performance over 2 hours of two backup systems you have been testing, where the results are as they appear in this table:

Time (Mins)	Backup 1 (%)	Backup 2 (%)
0	0	0
30	23	15
60	59	34
90	84	59
120	98	74
Scatter can be used to plot the backup performance for both systems, measured against the Y-axis, against the time, measured against the X-axis.

See also: gallery of chart styles.


More on hi-low charts

For example, a record of the temperature in central London, between 12 a.m. and 3 p.m., on July 31st, over a period of 5 years can be tabulated as follows:

Date	Low	Open	Close	High
31/7/93	26	28	26	29
31/7/94	30	31	32	33
31/7/95	26	27	29	29
31/7/96	31	34	33	35
31/7/97	24	30	30	33
The vertical bars in the chart show the extent of the High-Low difference (shown in blue below); the horizontal bars show the extent of the Open-Close difference:

Note that if the Close value is less than the Open value, the Open-Close difference is shown in red. If they are equal, a line is drawn.

You can create a chart without including the Open column (or row), though you should still maintain the order Low-Close-High. In this case, the Close value will be represented by a thin horizontal line.

Hi-Low-Close charts are especially useful for charting the highs and lows of daily share prices, as well as showing their closing value.

Note that in the above example, Chart assumes that the date column contains data, since dates are ultimately represented as numbers by Ability. Therefore make sure you switch First Column (or First Row where appropriate) to Labels. See vertical and horizontal series for more details.

See also: gallery of chart styles.


Data range

To change the data range that chart plots or alter the which way series run, select the Data Series command from the Chart menu. (Double-click on a chart to display this menu). Set any of the following:

•	Data Range. This is the range, including series and data point titles.

•	Display Series. Treat each data column (when set to Vertically) or data row (when set to Horizontally) as a series of values of a variable. See vertical and horizontal series for more details.

•	First Column. In the example below, the first column of the data range (A1..E4) obviously contains labels rather than data. If the first column contained numbers that you wanted treated as labels rather than data, this option will need to be set to Labels.

•	First Row. Determine whether the first row contains labels or data in the same way as First Column.


Vertical and horizontal series

Using the following example data:

With Display Series set at Horizontally, there are three series, namely Iron, Cobalt and Copper, each of which has a set of measurements or data points with which it is associated. The data points are the measurements for April, May, June and July. The different series are assigned different colors, which are maintained for each data point that is plotted throughout the chart. The data points are plotted measured against the scale along the left hand axis (the Y-axis):

To plot the chart with the months as series and the values for the metals as the data points, switch the Display Series setting to Vertically. There will now be four series, namely April, May, June and July, each with three associated data points:

See also: data range and series display


Titles and legends

To add titles to a chart and display legends:

1.	Select the Titles and Legends command from the Chart menu. (Double-click on a chart if you can't see this menu).

2.	Type in (or edit existing) text in any or all of the boxes for entering titles. Typically the top box will contain the main title for the chart, and the lower and side boxes can be used to enter titles for the axes.

3.	Select the font, font style, size and color for each of the title boxes.

4.	Select the Legend box to display the labels of the series. The different colors, patterns and shapes used to represent the series will also be shown.


Appearance

To set a background color for your chart:

1.	Select the Appearance command from the Chart menu. (Double-click on a chart if you can't see this menu).

2.	Choose from the following:

•	Select Chart to set the color for the inside of the chart.

•	Select Background to set the color for the area surrounding the chart.

You can also set the 3D appearance of the chart - see 3D view.


3D view

To display a chart in three dimensions:

1.	Select the Appearance command from the Chart menu. (Double-click on a chart if you can't see this menu).

2.	Click on the 3D box. Two more boxes are displayed.

3.	Scroll through the 3D Depth in % of X-axis box to fix the depth of the chart (the default depth is 65%). Setting this to 100% will make the chart as deep as it is tall.

4.	Click on the Full 3D View box to display two more boxes: for setting the Y angle and X angle. These rotate the chart so as to increase or decrease the angle from the Y-axis or X-axis respectively. Increasing the Y-angle has the effect of rotating the chart to the left; increasing the X-angle has the effect of tilting the chart forward. If you set both to zero, the chart is returned to a 2D view.

See also: setting colors for the chart.


Axes

To change the way the axes display, select the Axes command from the Chart menu. (Double-click on a chart to display this menu). Set any of the following:

•	Turn Automatic on or off. Applies to X and Y-axes. With automatic scaling on, the axis is scaled according to the data charted. With Automatic off, you'll have to specify a start point, end point and increment as below.

•	Start Value - start drawing the axis at this value.

•	End Value -  finish drawing the axis at this value.

•	Increment - determines how many markings there will be between the start and end values. The y-axis scale will adjust to reflect the values you set in the boxes.

For example, if you plot the numbers 100, 101 and 103, the chart, by default, will draw the Y-axis from 0 to 120 and, at this scale, each point will appear equal on the chart. To better display the data, set Automatic off, enter 90 as a Start Value, 110 as the End Value and 5 as the Increment.

•	Decimal Places - set the number of decimal places for the values marking the Y-axis.

•	To change from a linear to a logarithmic scale, switch from Linear to Logarithmic in the Y Axis Scale box.

Note that X-axis options only apply to Scatter (X vs. Y) charts.


Series attributes

To change the way chart displays each series, select Series from the Chart menu (double-click on a chart to display this menu).

You can modify individual series - make a selection in the series box and choose from the following:

•	To change the color, scroll through the Series Color box and click on the color you want the series to have.

•	Choose the pattern for the series in the Series Pattern box.

•	To rename a series click on the Rename button. If you display a legend for your chart, the series name shows here. See - titles and legends for more details.

•	Select a Marker Type for the series if the style of chart shows points.

If you want the values represented by the data points to be displayed in your chart, click on the Show Values box to check it. This effects all series.

If you want the points to be displayed, click on the Show Points box to check it. This effects  all series.


Editing a chart

Use the Chart menu to edit the chart properties. To see the chart menu:

•	For an embedded chart, double click in the main body of the chart. "Chart" becomes available as a standard menu item.

•	For an attached chart, the Chart menu will be visible as soon as the chart window is selected. For more information on working with attached charts, see attached charts.

Choose from the following properties:

Style

Data Range

Titles and Legends

Appearance

Axes

Series

See also:

Deleting a chart

Resizing a chart


Attached charts

When creating a chart, you are given the choice whether to insert the chart into the current spreadsheet window (On This View) or into another window that is connected to the current spreadsheet (As New View) - see creating a chart.

If you choose On This View, the chart will be embedded in the current spreadsheet and will remain visible in the spreadsheet as you work and you can resize it and move it - see resizing and moving an embedded chart.

If you choose As New View, the chart will have a window to itself but is attached to the spreadsheet the chart was based on. When you create and attached chart, you'll see two entries for the spreadsheet under the Windows menu. This allows you to switch between the chart and the data. You would get additional entries under the Windows menu for each additional chart you created as a new view.

With an attached chart, you can split the screen to show the underlying spreadsheet - use the Split command on the Window menu and position the splitter bars to divide window into two. See splitting a spreadsheet) for more information.


Resizing and moving an embedded chart

Once a chart is embedded in a spreadsheet you can edit, resize or move it. The chart is in fixed mode when there are no handles showing along its border. To move or resize the chart, click once in the main body of the chart. Handles will appear at various place along the chart's border.

Move the chart by holding down the left hand mouse button when the cross cursor is showing over the body of the chart. A chart outline will be displayed. Drag this to where you want the chart to appear and release the mouse button. The chart will be displayed in its new position.

Resize the chart by pointing at one of the handles and holding down the left hand mouse button when the cursor changes to a double-headed arrow. An up-down arrow is displayed along the top and bottom borders; a left-right arrow along the left and right borders; and a diagonal arrow at the corners. You can use these to increase or decrease the height, width, or height and width together of the chart. Drag the border till it is the size you require and then release the mouse button. The chart will be displayed with its new dimensions.


Deleting a chart from a spreadsheet

To delete an embedded chart from a spreadsheet, click on the chart once to select it and then press the Del key.

To delete an attached chart, display the chart window (select it from the Window menu) and select the close box [image] at the top right of the window. The chart window will be closed and the chart deleted without having any effect on the spreadsheet.


Printing a chart

You can print an embedded chart by printing the spreadsheet in which it appears. The spreadsheet, along with the embedded chart, will be printed. Select Print from the toolbar or the File menu.

To print a chart on its own it has to have been set up as an attached chart. Go to the window in which the attached chart is placed and select the Print button [image] or select the Print command from the toolbar or the File menu.